﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.ServiceModel;
using System.Text;
using System.Threading;
using BLToolkit.Data.DataProvider;
using ICSharpCode.SharpZipLib.Zip;
using nwrole.toolbox.sync.ServiceHost;
using nwrole.toolbox.sync.Utils;
using nwrole.toolbox.sync.client;
using nwrole.toolbox.sync.contract;
using nwrole.toolbox.sync.entity;
using nwrole.lib;
using BLToolkit.Data;
using System.Data;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;

namespace nwrole.toolbox.sync.service
{
    public class SyncSrcServiceParams
    {
        public string SrcDbConnectionString { get; set; }
        public string SyncDbConnectionString { get; set; }
        public ServiceConnectionInfo SyncDestServiceConnInfo { get; set; }
        public DataProviderType DataProviderType { get; set; }
        public TimeSpan SyncLockSpan { get; set; }
        public SyncSrcServiceParams() {}

        public SyncSrcServiceParams(string srcDbConnectionStr, string syncDbConnectionStr, ServiceConnectionInfo syncDestServiceConnInfo, DataProviderType dataProviderType, TimeSpan syncLockSpan)
        {
            SrcDbConnectionString = srcDbConnectionStr;
            SyncDbConnectionString = syncDbConnectionStr;
            SyncDestServiceConnInfo = syncDestServiceConnInfo;
            DataProviderType = dataProviderType;
            SyncLockSpan = syncLockSpan;
        }
    }

    [ServiceBehavior(IncludeExceptionDetailInFaults=true)]
    public class SyncSourceService : ISyncSourceService, IParamService<SyncSrcServiceParams>
    {
        public string SrcDbConnectionString { get; set; }
        public string SyncDbConnectionString { get; set; }
        public ServiceConnectionInfo SyncDestServiceConnInfo { get; set; }
        public DataProviderType DataProviderType { get; set; }
        public TimeSpan SyncLockSpan = new TimeSpan(0, 5, 0);

        private void Log(string s)
        {
            Trace.TraceInformation(s);

            string fileName = @"C:\Program Files\nwrole\nwrole Supervisor Service\log\PluginService\log.log";
            try
            {
                /*System.IO.File.AppendAllText(fileName,
                    string.Format("{0}: {1}\r\n", DateTime.Now.ToString("yyyy-MM-dd"), s));*/
            }
            catch
            {
                
            }
        }

        public void InitParamService(SyncSrcServiceParams serviceParams)
        {
            //srcDbConnectionString = (string)userData[0];
            //syncDestServiceConnInfo = (ServiceConnectionInfo)userData[1];

            SrcDbConnectionString = serviceParams.SrcDbConnectionString;
            SyncDbConnectionString = serviceParams.SyncDbConnectionString;
            SyncDestServiceConnInfo = serviceParams.SyncDestServiceConnInfo;
            DataProviderType = serviceParams.DataProviderType;
            SyncLockSpan = serviceParams.SyncLockSpan;
        }

        private DataProviderBase CreateDataProvider()
        {
            switch (DataProviderType)
            {
                case DataProviderType.Sql:
                    return new SqlDataProvider();
                default:
                    return null;
            }
        }

        /// <summary>
        /// Prepares for a new full sync. You then need to continously call SyncNextEntity() until it returns false.
        /// </summary>
        /// <param name="instanceId"></param>
        /// <returns>Returns true if you should begin calling SyncNextEntity. Returns false when a full sync is already running. Do NOT call SyncNextEntity at all, the running sync will sync again for you.</returns>
        public bool StartFullSync(int instanceId)
        {
            Log("SyncSourceService StartFullSync");

            var repo = GetRepository();
            SyncInstance syncInstance = repo.FindSyncInstance(instanceId);
            //var entityList = repo.FindRemainingToSync(instanceId);

            DateTime dbDateTime = repo.GetDbDateTime();

            bool readyToSync;
            // Log("SyncSourceService StartFullSync entityList count: " + entityList.Count);
            //if (entityList.Count > 0)
            if (syncInstance.LockedUntil.HasValue && syncInstance.LockedUntil.Value > dbDateTime)
            {
                // If already running a full sync, flag to run it again afterwords
                syncInstance.RunFullSyncAgain = true;
                readyToSync = false;
            }
            else
            {
                // Start a new full sync
                syncInstance.LastFullSyncStart = dbDateTime;
                syncInstance.RunFullSyncAgain = false; // reset the flag in case of earlier problems
                syncInstance.LockedUntil = dbDateTime + SyncLockSpan;
                readyToSync = true;
            }

            repo.Update(syncInstance);
            Log("SyncSourceService StartFullSync done");
            return readyToSync;
        }

        public bool SyncNextEntity(int instanceId)
        {
            var repo = GetRepository();

            var entityList = repo.FindRemainingToSync(instanceId);
            Log("SyncSourceService SyncNextEntity entityList count: " + entityList.Count);
            if(entityList.Count > 0)
            {
                SyncEntity entity = entityList.First();

                SyncDestinationServiceClient destinationService = new SyncDestinationServiceClient(SyncDestServiceConnInfo);

                Log("Sync " + entity.Name + " ....");
                var result = syncTable(40000, entity, destinationService);
                if (result != null)
                    Log(" count:" + result.RowCount + " Done.");
                else
                    Log("result was null");
            }

            // Check if there are more left to sync (not counting this one)
            if (entityList.Count > 1)
                return true;

            // All done

            SyncInstance syncInstance = repo.FindSyncInstance(instanceId);

            DateTime dbDateTime = repo.GetDbDateTime();

            if(syncInstance.RunFullSyncAgain)
            {
                // Start a new full sync
                
                syncInstance.LastFullSyncStart = dbDateTime;
                syncInstance.FullSyncCompleted = dbDateTime.AddSeconds(-1); // -1s to make it different to the new full sync start
                syncInstance.RunFullSyncAgain = false;
                syncInstance.LockedUntil = dbDateTime + SyncLockSpan;
                repo.Update(syncInstance);

                // Check again so there is anything to sync
                var newEntityList = repo.FindRemainingToSync(instanceId);
                if (newEntityList.Count > 0)
                    return true;
            }

            syncInstance.FullSyncCompleted = dbDateTime;
            syncInstance.LockedUntil = null;
            repo.Update(syncInstance);
            
            return false;
        }

        /// <summary>
        /// Will sync all enabled SyncEntities
        /// </summary>
        /*public String SyncAllSyncEntities()
        {
            //var rep = GetRepository();
            //var entities = rep.FindAllToSync();

            //SyncDestinationServiceClient destinationService = new SyncDestinationServiceClient(syncDestServiceConnInfo);

            //foreach (var entity in entities)
            //{
            //    Console.Write("Sync " + entity.Name + " ....");
            //    var result = syncTable(100000, entity, destinationService);
            //    if (result != null)
            //        Console.WriteLine(" count:" + result.RowCount + " Done.");
            //    else
            //        Console.WriteLine("result was null");
            //}

            //return null;

            //Thread thread = new Thread(InternalSyncAll);
            //thread.Start();
            InternalSyncAll();

            return null;
        }

        private void InternalSyncAll()
        {
            var rep = GetRepository();
            var entities = rep.FindAllToSync();

            SyncDestinationServiceClient destinationService = new SyncDestinationServiceClient(SyncDestServiceConnInfo);

            foreach (var entity in entities)
            {
                Console.Write("Sync " + entity.Name + " ....");
                var result = syncTable(40000, entity, destinationService);
                if (result != null)
                    Console.WriteLine(" count:" + result.RowCount + " Done.");
                else
                    Console.WriteLine("result was null");
            }
        }*/

        /// <summary>
        /// Will only sync if the SyncEntity is enabled
        /// </summary>
        /// <param name="id">SyncEntity Id</param>
        public String SyncSyncEntity(long id)
        {
            Trace.TraceInformation("SyncSourceService SyncSyncEntity id: " + id);

            var rep = GetRepository();
            var entity = rep.FindByIdWithRef(id);
            if (entity.SyncEnabled == 0)
                return null;

            Log("Sync " + entity.Name + ":");
            //GhostTableSync sync = GetGhostTableSync(100000);

            SyncDestinationServiceClient destinationService = new SyncDestinationServiceClient(SyncDestServiceConnInfo);
            /*//EndpointAddress ep = new EndpointAddress("http://localhost:9004/hejsan.svc");
            EndpointAddress ep = new EndpointAddress(syncDestServiceUrl);
            ISyncDestinationService destinationService = ChannelFactory<ISyncDestinationService>.CreateChannel(new BasicHttpBinding(), ep);*/

            syncTable(40000, entity, destinationService);

            return null;
        }

        private SyncResult syncTable(int batchSize, SyncEntity map, SyncDestinationServiceClient destinationService)
        {
            //GetRepository().TestTimeStamp();
            try
            {
                if (map.KeepDays > 0)
                {
                    destinationService.DeleteOldRowsFromDestinationTable(map);
                }

                Guid guid = Guid.NewGuid();
                int messageCounter = 0;
                var result = SyncWithReaderAndDirectUpdate(map, destinationService, (bytes, tableName, schema) =>
                {
                    //Log("syncTable 1");
                    SyncMessageEnvelope msg = new SyncMessageEnvelope(tableName, "ibbe3", bytes, schema, guid);
                    //Log("NU FINNS ETT SYNKMESSAGE");
                    destinationService.ProcessMessageOld(msg);
                    //Log("NU ÄR MEDDELANDET PROCESSAT");
                    messageCounter++;
                    return 1;
                });
                Log("syncTable rowCount: " + result.RowCount);
                Log("" + result.RowCount);

                if(result.RowCount > 0)
                {
                    SyncCompleteEnvelope completetMsg = new SyncCompleteEnvelope();
                    completetMsg.MsgCorr = guid;
                    completetMsg.DestinationSyncCompleteSPName = map.DestinationSyncCompleteSPName;
                    completetMsg.MessageCount = messageCounter;

                    destinationService.SyncComplete(completetMsg);
                    Log("syncTable syncComplete sent");
                }

                Log("syncTable newSyncDat: " + (result.newSyncDat.HasValue ? result.newSyncDat.Value.ToString() : "null"));
                if(result.newSyncDat.HasValue)
                    map.SyncDat = result.newSyncDat.Value;
                if (result.HighestTimestamp != null && result.HighestTimestamp.Length > 0)
                    map.HighestTimestamp = result.HighestTimestamp;
                if (result.LatestFileSync.HasValue)
                    map.LatestFileSync = result.LatestFileSync;

                //Trace.TraceInformation("syncTable update entity");
                SyncEntity.Update(map, SrcDbConnectionString, SyncDbConnectionString);
                //Trace.TraceInformation("syncTable done");
                return result;
            }
            catch (Exception e)
            {
                Log("SyncSourceService syncTable error: " + e.ToString());
            }
            return null;
        }

        private SyncResult SyncWithReaderAndDirectUpdate(SyncEntity map, SyncDestinationServiceClient destinationService, Func<byte[], string, string, int> callback)
        {
            Log("SyncSourceService SyncWithReaderAndDirectUpdate Start " + map.Name + " mode: " + map.SyncMode);

            SqlBuilder sqlBuilder = new SqlBuilder();

            SyncResult result = new SyncResult();
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(CreateDataProvider(), SrcDbConnectionString))
            {
                if(map.SyncMode == SyncModeEnum.FileSync)
                {
                    db.SetCommand("select getdate()");
                    DateTime dbDateTime = (DateTime)db.ExecuteScalar();
                    DateTime latestFileSync = DateTime.Now;
                    result.RowCount = 0;

                    var fileDatas = FastDirectoryEnumerator.EnumerateFiles(map.SourceTableName, map.SourceFilter, SearchOption.AllDirectories).Where(f => !map.LatestFileSync.HasValue || f.LastWriteTime > map.LatestFileSync);
                    if(!fileDatas.Any())
                    {
                        result.newSyncDat = dbDateTime;
                        result.LatestFileSync = latestFileSync;
                        return result;
                    }

                    MemoryStream zipStream = new MemoryStream();
                    ZipFile zip = ZipFile.Create(zipStream);

                    //initialize the file so that it can accept updates
                    zip.BeginUpdate();

                    int srcPathLength = Path.GetFullPath(map.SourceTableName).Length + 1;

                    // Log("SyncSourceService SyncWithReaderAndDirectUpdate FileSync path with slash: " + pathWithSlash + " length:" + srcPathLength);

                    // add the files to the zip file
                    foreach(var fileData in fileDatas)
                    {
                        string relFileName = fileData.Path.Substring(srcPathLength);
                        Log("SyncSourceService SyncWithReaderAndDirectUpdate FileSync path: " + fileData.Path + " file: " + fileData.Name + " rel: " + relFileName);

                        zip.Add(fileData.Path, relFileName);
                        result.RowCount++;
                    }

                    //commit the update once we are done
                    zip.CommitUpdate();
                    //close the file
                    zip.Close();

                    Guid fileId = Guid.NewGuid();
                    long zipSize = zipStream.Length;

                    //Trace.TraceInformation("File size: " + zipSize);

                    zipStream.Seek(0, SeekOrigin.Begin);

                    destinationService.InitFileTransfer(fileId, zipSize, map.DestinationTableName);
                    
                    int maxChunkSize = 4096;
                    byte[] fileBuffer = new byte[maxChunkSize];
                    int readPos = 0;
                    long part = 1;
                    while (true)
                    {
                        int chunkSize = readPos + maxChunkSize <= zipSize ? maxChunkSize : (int)zipSize - readPos;
                        int readBytes = zipStream.Read(fileBuffer, 0, chunkSize);

                        if (readBytes > 0)
                        {
                            //Trace.TraceInformation("Sending file part: {0} bytes: {1} currPos: {2}", part, readBytes, readPos);
                            destinationService.SendFilePart(fileId, readPos, readBytes, fileBuffer);
                            readPos += readBytes;
                            part++;
                        }

                        if (readBytes == 0 || readPos >= zipSize)
                        {
                            //Trace.TraceInformation("File done sent: " + readPos);
                            break;
                        }
                    }

                    result.newSyncDat = dbDateTime;
                    result.LatestFileSync = latestFileSync;
                    return result;
                }

                if (map.Name == "FAST_TEXTER")
                {
                    try
                    {
                        string dropSql = sqlBuilder.DropOracleTable(GlobalSettings.SqlTablePrefix + ".TEXTER_CLOB");
                        db.SetCommand(dropSql); // "drop table dbo.TEXTER_CLOB");
                        db.ExecuteNonQuery();

                        string sql = @"
create GLOBAL TEMPORARY table " + GlobalSettings.SqlTablePrefix + @".TEXTER_CLOB ON COMMIT PRESERVE ROWS
as select TEXTERID, TEXTERTYPID, TABELLNAMN, RELID, to_lob(text) as TEXT_CLOB 
from wf.texter
where tabellnamn = 'Objekt'";
                        db.SetCommand(sql);
                        db.ExecuteNonQuery();
                    }
                    catch
                    {
                    }

                }

                if (map.Name.ToLower().Contains("OBJEKTSBUNDET".ToLower()))
                {
                    db.SetCommand("update " + GlobalSettings.SqlTablePrefix + ".OBJEKTSBUNDET_GHOST set BELOPP = 130 where OBJEKTSBUNDETID = 21");
                    db.ExecuteNonQuery();
                }

                // db.SetCommand("delete from dbo.OBJEKTSBUNDET_COPY");
                // db.ExecuteNonQuery();

                if (!string.IsNullOrEmpty(map.SourceSyncStartSPName))
                {
                    // Run Source-Sync-Start procedure if one is defined
                    db.SetCommand(map.SourceSyncStartSPName);
                    db.ExecuteNonQuery();
                }

                // save the syncdate
                DateTime tmp_SYNCDAT = DateTime.Now;
                DateTime SYNCDAT = new DateTime(tmp_SYNCDAT.Year, tmp_SYNCDAT.Month, tmp_SYNCDAT.Day, tmp_SYNCDAT.Hour, tmp_SYNCDAT.Minute, tmp_SYNCDAT.Second);

                if (map.SyncMode == SyncModeEnum.GhostSync)
                {
                    db.SetCommand("select getdate()");
                    result.newSyncDat = (DateTime)db.ExecuteScalar();

                    Trace.TraceInformation("SyncWithReaderAndDirectUpdate GhostSync SYNCDAT:" + SYNCDAT);
                    if (map.ResyncAll == 1)
                    {
                        string deleteSql = sqlBuilder.DeleteFromShadowTable(map);
                        db.SetCommand(deleteSql);
                        db.ExecuteNonQuery();
                        map.ResyncAll = 0;
                    }

                    // update ghost table
                    string directUpsertSql = sqlBuilder.DirectUpsertShadowTable(map);

                    /*if (directUpsertSql.ToLower().Contains("KUNDINTRESSENTTYP".ToLower()))
                    {
                    }*/

                    //Trace.TraceInformation("SyncWithReaderAndDirectUpdate upsert: " + directUpsertSql);

                    db.SetCommand(directUpsertSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "SYNCDAT1", SYNCDAT), db.Parameter(GlobalSettings.SqlVariablePrefix + "SYNCDAT2", SYNCDAT));
                    db.ExecuteNonQuery();

                    // get changes from ghost table
                    string selectSql = sqlBuilder.SelectFromShadowTableBySYNCDAT(map);

                    //Trace.TraceInformation("SyncWithReaderAndDirectUpdate select: " + selectSql);

                    db.SetCommand(selectSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "SYNCDAT", SYNCDAT));
                }
                else if (map.SyncMode == SyncModeEnum.LastChangedSync)
                {
                    //db.SetCommand("select getdate()");
                    
                    string extraFilter = map.SourceFilter;

                    if (!string.IsNullOrEmpty(map.SourceTimestampColumnName))
                    {
                        // Sync using TIMESTAMP

                        db.SetCommand("select getdate()");
                        result.newSyncDat = (DateTime)db.ExecuteScalar();

                        // First get the highest timestamp from the result
                        string maxSql = sqlBuilder.MaxTimestampFromSourceTableByTimestamp(map, extraFilter, map.OverrideFilter);
                        db.SetCommand(maxSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "HighestTimestamp", map.HighestTimestamp, DbType.Binary));
                        result.HighestTimestamp = db.ExecuteScalar<System.Data.Linq.Binary>();

                        string selectSql = sqlBuilder.SelectFromSourceTableByTimestamp(map, extraFilter, map.OverrideFilter);

                        db.SetCommand(selectSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "HighestTimestamp", map.HighestTimestamp, DbType.Binary));
                    }
                    else
                    {
                        // Sync using SYNCDAT (Datetime)

                        //db.SetCommand("select dateadd(minute, -1, getdate())");
                        db.SetCommand("select getdate()");
                        result.newSyncDat = (DateTime)db.ExecuteScalar();

                        DateTime lastChanged = map.SyncDat.HasValue ? map.SyncDat.Value : new DateTime(1900, 1, 1);

                        string selectSql = sqlBuilder.SelectFromSourceTableBySYNCDAT(map, extraFilter, map.OverrideFilter);

                        db.SetCommand(selectSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "SYNCDAT", lastChanged.AddMinutes(-1)));
                    }

                    if (map.ResyncAll == 1)
                    {
                        map.ResyncAll = 0;
                    }

                }

                IDataReader reader = db.ExecuteReader();

                BinaryFormatter serializationFormatter = new BinaryFormatter();
                MemoryStream buffer = new MemoryStream();
                string schema = map.GetSchema();
                object[] row = null;
                while (reader.Read())
                {
                    result.RowCount++;
                    if (row == null)
                    {
                        row = new object[reader.FieldCount];
                    }

                    // copy from reader to object[]
                    for (int t = 0; t < reader.FieldCount; t++)
                    {
                        var col = map.Columns[t];
                        try
                        {
                            row[t] = reader[t];
                        }
                        catch
                        {
                        }

                    }

                    serializationFormatter.Serialize(buffer, row);
                    if (buffer.Length > 1000000)
                    {
                        callback(buffer.ToArray(), map.Name, schema);
                        buffer.SetLength(0);
                    }

                }

                if (buffer.Length > 0)
                {
                    callback(buffer.ToArray(), map.Name, schema);
                    buffer.SetLength(0);
                }

            }

            return result;
        }

        public String DeploySource(long id)
        {
            var rep = GetRepository();
            var entity = rep.FindByIdWithRef(id);

            /*GhostTableSync sync = GetGhostTableSync(1000);
            sync.CreateGhostInt(entity, true);*/

            CreateGhostTable(entity, true);

            return null;
        }

        public String DeleteSyncEntity(long id)
        {
            var rep = GetRepository();
            var entity = rep.FindByIdWithRef(id);
            if (entity != null)
            {
                rep.Delete(entity);
            }
            return null;
        }

        /*public String DeployDestEnvironment()
        {
            GhostTableSync sync = GetGhostTableSync(1000);
            sync.CreateDestEnvironment();

            return null;
        }*/

        public SyncEntity ImportSchema(int syncInstanceId, long schemaId, short type)
        {
            Trace.TraceInformation("SyncSourceService ImportSchema 1");

            var rep = GetRepository();
            var schemaEntity = rep.SchemaFindById(schemaId, type);
            var syncEntity = rep.FindBySourceTableName(schemaEntity.Name, true);

            if (syncEntity == null)
            {
                Trace.TraceInformation("SyncSourceService ImportSchema 2");
                syncEntity = new SyncEntity();

                syncEntity.SyncInstanceId = syncInstanceId;
                syncEntity.Name = schemaEntity.Name;
                syncEntity.SourceTableName = schemaEntity.Name;
                syncEntity.DestinationTableName = "q_INT_" + schemaEntity.Name;
                syncEntity.ShadowTableName = "q_" + schemaEntity.Name + "_shadow";
                syncEntity.SyncEnabledBool = false;
                
                rep.Insert(syncEntity);
            }
            else
            {
                // Remove columns that no longer exist in the database
                List<SyncEntityColumn> columnsToDelete = new List<SyncEntityColumn>();
                foreach(SyncEntityColumn existingCol in syncEntity.Columns)
                {
                    SyncEntityColumn schemaCol = schemaEntity.Columns.FirstOrDefault(c => c.ColumnName == existingCol.ColumnName);
                    if(schemaCol == null)
                    {
                        columnsToDelete.Add(existingCol);
                    }
                }

                rep.DeleteColumns(syncEntity, columnsToDelete);
            }
            Trace.TraceInformation("SyncSourceService ImportSchema 3");
            
            // go through all the table's columns
            foreach (SyncEntityColumn col in schemaEntity.Columns)
            {
                SyncEntityColumn currentCol = syncEntity.Columns.FirstOrDefault(c => c.ColumnName == col.ColumnName);
                if (currentCol != null)
                {
                    // already exist, but import it again
                    currentCol.Import(col);
                    currentCol.SyncEntityId = syncEntity.Id;

                }
                else
                {
                    // Doesn't exist, import it
                    currentCol = new SyncEntityColumn();
                    currentCol.Import(col);
                    currentCol.SyncEntityId = syncEntity.Id;
                    //currentCol.SyncEntity = syncEntity;
                    syncEntity.Columns.Add(currentCol);
                }
            }
            Trace.TraceInformation("SyncSourceService ImportSchema 4");
            syncEntity.ImportDat = DateTime.Now;
            rep.Update(syncEntity);
            Trace.TraceInformation("SyncSourceService ImportSchema 5");
            return syncEntity;
        }

        public SyncEntity GetSchemaEntity(long id, short type)
        {
            try
            {
                Trace.TraceError("GetSchemaEntity");
                SyncEntity e = GetRepository().SchemaFindById(id, type);
                return e;

            }
            catch (Exception e)
            {
                Trace.TraceError("SyncSourceService GetSchemaEntity Error: " + e);
            }
            return null;
        }

        public List<SyncEntity> GetSchemaEntities(int page = 0, int pagesize = 1500)
        {
            try
            {
                Trace.TraceInformation("GetSchemaEntities");
                List<SyncEntity> list = GetRepository().SchemaFindAll().Skip(page * pagesize).Take(pagesize).ToList(); // TEMP 121126 don't take all because we go beyond some data limit
                //List<SyncEntity> list = GetRepository().SchemaFindAll().ToList(); // TEMP 121126 don't take all because we go beyond some data limit
                Trace.TraceInformation("GetSchemaEntities returns " + list.Count + " entities");
                return list;

            }
            catch (Exception e)
            {
                Trace.TraceError("SyncSourceService GetSchemaEntities Error: " + e);
            }
            return null;

        }

        public bool ToggleSyncEnabled(long id)
        {
            var rep = GetRepository();
            SyncEntity entity = rep.SyncEntity_FindById(id);

            entity.SyncEnabledBool = !entity.SyncEnabledBool;
            rep.Update(entity);

            return entity.SyncEnabledBool;
        }

        public SyncModeEnum ToggleSyncMode(long id)
        {
            var rep = GetRepository();
            SyncEntity entity = rep.SyncEntity_FindById(id);

            if(entity.SyncMode == SyncModeEnum.GhostSync)
                entity.SyncMode = SyncModeEnum.LastChangedSync;
            else
                entity.SyncMode = SyncModeEnum.GhostSync;
            
            rep.Update(entity);

            return entity.SyncMode;
        }

        public bool ToggleSyncColumnEnabled(long id)
        {
            var rep = GetRepository();
            SyncEntityColumn col = rep.SyncEntityColumn_FindById(id);

            col.SyncEnabledBool = !col.SyncEnabledBool;
            rep.Update(col);

            return col.SyncEnabledBool;
        }

        public void ToggleAllSyncColumnEnabled(long syncEntityId)
        {
            var rep = GetRepository();
            //List<SyncEntityColumn> cols = rep.SyncEntityColumns_FindByEntityId(syncEntityId);
            SyncEntity entity = rep.SyncEntity_FindById(syncEntityId);
            entity.Columns = rep.SyncEntityColumns_FindByEntityId(syncEntityId);

            foreach(SyncEntityColumn col in entity.Columns)
            {
                col.SyncEnabledBool = !col.SyncEnabledBool;
            }
            
            rep.Update(entity);
        }

        public void Update(SyncEntity entity)
        {
            Log("Update");
            //SyncEntity e = (new SyncEntityDemoRepository()).GetSyncEntity();
            GetRepository().Update(entity);
        }

        public List<SyncEntity> GetEntities(int syncInstanceId)
        {
            try
            {
                Trace.TraceInformation("GetEntities");
                // List<SyncEntity> list = (new SyncEntityDemoRepository()).GetSyncEntityList();
                List<SyncEntity> list = GetRepository().FindAll(syncInstanceId);
                Trace.TraceInformation("GetEntities returns " + list.Count + " entities");
                return list;
            }
            catch (Exception e)
            {
                Trace.TraceError("SyncSourceService GetEntities Error: " + e);
            }
            return null;
        }

        /*public SyncEntity GetEntityByName(string name)
        {
            Console.WriteLine("GetEntityByName");
            //SyncEntity e = (new SyncEntityDemoRepository()).GetSyncEntity();
            SyncEntity e = GetRepository().FindByIdWithRef(1);
            return e;
        }*/

        public SyncEntity GetEntityById(long id)
        {
            Console.WriteLine("GetEntityById");
            //SyncEntity e = (new SyncEntityDemoRepository()).GetSyncEntity();
            SyncEntity e = GetRepository().FindByIdWithRef(id);
            return e;
        }

        public DeployStatus GetDeployStatus()
        {
            DeployStatus status = DeployStatus.NotInstalled;

            try
            {
                SyncEntityRepository repo = GetRepository();
                string version = repo.GetSettingStringValue("Version");
                if (!string.IsNullOrEmpty(version))
                    status |= DeployStatus.SettingsTable;

                if(repo.TableExist("SyncInstance"))
                    status |= DeployStatus.InstanceTable;

                if (repo.TableExist("SyncEntity"))
                    status |= DeployStatus.EntityTable;

                if (repo.TableExist("SyncEntityColumn"))
                    status |= DeployStatus.EntityColumnTable;
            }
            catch
            {
                
            }

            return status;
        }

        public string GetSettingStringValue(string key)
        {
            return GetRepository().GetSettingStringValue(key);
        }

        public void SetSettingValue(string key, string value)
        {
            GetRepository().SetSettingValue(key, value);
        }

        public SyncEntityRepository GetRepository()
        {
            SyncEntityRepository dal = new SyncEntityRepository();
            dal.SrcConnectionString = SrcDbConnectionString;
            dal.SyncConnectionString = SyncDbConnectionString;
            dal.DataProvider = new SqlDataProvider();
            return dal;
        }

        private void CreateGhostTable(SyncEntity map, bool drop)
        {
            if (map.SourceTableName.Equals(map.ShadowTableName, StringComparison.CurrentCultureIgnoreCase))
            {
                throw new Exception("map.SourceTableName.Equals(map.ShadowTableName)");
            }

            SqlBuilder sqlBuilder = new SqlBuilder();

            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(CreateDataProvider(), SrcDbConnectionString)) // TODO: Should we really hardcode the data provider?
            {
                if (drop)
                {
                    string dropTableSql = sqlBuilder.DropGhostTable(map);
                    db.SetCommand(dropTableSql);
                    db.ExecuteNonQuery();
                }

                string createTableSql = sqlBuilder.CreateGhostTable(map);
                db.SetCommand(createTableSql);
                db.ExecuteNonQuery();
            }
        }

        public bool DeploySourceEnvironment()
        {
            SqlBuilder sqlBuilder = new SqlBuilder();

            try
            {
                DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
                using (DbManager db = new DbManager(CreateDataProvider(), SyncDbConnectionString))
                {
                    string createSyncConfigTable = sqlBuilder.CreateSyncSettingTable();
                    db.SetCommand(createSyncConfigTable).ExecuteNonQuery();

                    string createSyncInstanceTable = sqlBuilder.CreateSyncInstanceTable();
                    db.SetCommand(createSyncInstanceTable).ExecuteNonQuery();

                    string createSyncEntityTable = sqlBuilder.CreateSyncEntityTable();
                    db.SetCommand(createSyncEntityTable).ExecuteNonQuery();

                    string createSyncEntityColumnTable = sqlBuilder.CreateSyncEntityColumnTable();
                    db.SetCommand(createSyncEntityColumnTable).ExecuteNonQuery();
                }

                SyncEntityRepository repo = GetRepository();
                List<SyncInstance> syncInstances = repo.FindAllSyncInstances();
                if(syncInstances.Count == 0)
                {
                    SyncInstance instance = new SyncInstance();
                    instance.Name = "Default";
                    repo.Insert(instance);
                }

                repo.SetSettingValue("Version", "1.0.0");
            }
            catch(Exception e)
            {
                Trace.TraceError("SyncSourceService DeploySourceEnvironment Error: " + e);
                return false;
            }

            return true;
        }
    }
}
